|
Rsyslog : Output Logs to Database
2015/06/18 |
|
Configure Rsyslog to output logs to Database.
|
|
| [1] |
It's possible to select a database from some mainly used products in the world, this example shows to configure with MariaDB,
so Install and start MariaDB server, refer to here.
|
| [2] | Create a user and Database for Rsyslog. |
|
[root@dlp ~]#
[root@dlp ~]# yum -y install rsyslog-mysql cat /usr/share/doc/rsyslog-mysql-*/createDB.sql | mysql -u root -p Enter password: [root@dlp ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7291 Server version: 5.5.41-MariaDB MariaDB Server Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # create "rsyslog" user and grant privileges him to Syslog DB ( set any password for 'password' section) MariaDB [(none)]> grant all privileges on Syslog.* to rsyslog@'localhost' identified by 'password'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> exit Bye |
| [3] | Configure Rsyslog to output logs to database. |
|
[root@dlp ~]#
vi /etc/rsyslog.conf # near line 22: add $ModLoad ommysql
# for example, output logs for "authpriv.*" # how to wite ⇒ :ommysql:Host,DB,DBUser,DBPassword authpriv.* :ommysql:localhost,Syslog,rsyslog,password
systemctl restart rsyslog
|
| [4] | After configuration of above, some logs of kinds of authentication are recorded on Database like follows. |
|
[root@dlp ~]# mysql -u rsyslog -p Syslog Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 7299 Server version: 5.5.41-MariaDB MariaDB Server Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [Syslog]> show tables; +------------------------+ | Tables_in_Syslog | +------------------------+ | SystemEvents | | SystemEventsProperties | +------------------------+ 2 rows in set (0.00 sec)MariaDB [Syslog]> select ReceivedAt,Facility,Priority,FromHost,Message from SystemEvents; +---------------------+----------+----------+----------+--------------------------------------------------------+ | ReceivedAt | Facility | Priority | FromHost | Message | +---------------------+----------+----------+----------+--------------------------------------------------------+ | 2015-06-17 19:40:33 | 10 | 6 | dlp | pam_unix(login:session): session closed for user root | | 2015-06-17 19:40:39 | 10 | 6 | dlp | pam_unix(login:session): session opened for user root | | 2015-06-17 19:40:39 | 10 | 6 | dlp | DIALUP AT ttyS0 BY root | | 2015-06-17 19:40:39 | 10 | 5 | dlp | ROOT LOGIN ON ttyS0 | | 2015-06-17 19:40:58 | 10 | 6 | node01 | Accepted password for cent from 10.0.0.30 port 60492 | | 2015-06-17 19:40:58 | 10 | 6 | node01 | pam_unix(sshd:session): session opened for user cent | | 2015-06-17 19:40:58 | 10 | 6 | node01 | Received disconnect from 10.0.0.30: 11: disconnected | | 2015-06-17 19:40:58 | 10 | 6 | node01 | pam_unix(sshd:session): session closed for user cent | | 2015-06-17 19:41:13 | 10 | 6 | node01 | pam_unix(su-l:session): session opened for user cent | | 2015-06-17 19:41:23 | 10 | 6 | dlp | Invalid user cent from 10.0.0.51 | | 2015-06-17 19:41:23 | 10 | 6 | dlp | input_userauth_request: invalid user cent [preauth] | | 2015-06-17 19:41:27 | 10 | 4 | dlp | pam_unix(sshd:auth): check pass; user unknown | | 2015-06-17 19:41:27 | 10 | 5 | dlp | pam_unix(sshd:auth): authentication failure; logname= | | 2015-06-17 19:41:28 | 10 | 6 | dlp | Failed password for invalid user cent from 10.0.0.51 p | | 2015-06-17 19:41:29 | 10 | 6 | dlp | Connection closed by 10.0.0.51 [preauth] | | 2015-06-17 19:41:40 | 10 | 6 | dlp | Accepted password for root from 10.0.0.51 port 58750 s | | 2015-06-17 19:41:40 | 10 | 6 | dlp | pam_unix(sshd:session): session opened for user root b | | 2015-06-17 19:41:42 | 10 | 6 | dlp | Received disconnect from 10.0.0.51: 11: disconnected b | | 2015-06-17 19:41:42 | 10 | 6 | dlp | pam_unix(sshd:session): session closed for user root | +---------------------+----------+----------+----------+--------------------------------------------------------+ 19 rows in set (0.00 sec) |